﻿using System;
using System.Collections.Generic;
using System.Data;

namespace Finance.Data.Entities
{
	public interface IDebtDocuments : IDataBaseObjectCollection<IDebtDocument>
	{
		IList<IDebtDocument> RetrieveDocuments(DateTime date);

		IList<IDebtDocument> RetrieveDocuments(DocumentType documentType);
	}

	internal class DebtDocuments : DataBaseObjectCollection<IDebtDocument>, IDebtDocuments
	{
		private const string InsertDocumentQuery =
			"insert into debtdocument (docdatetime, amount, typecode, documentsubtypeid, Description, contractorId, id, rate) " +
			"values (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7)";

		private const string UpdateDocumentQuery =
			"update debtdocument set amount = @p0, documentsubtypeid = @p1, description = @p2, contractorId = @p3, docdatetime = @p4, rate = @p6 where id = @p5";

		private const string SelectBaseQuery =
			"select id, docdatetime, amount, Description, name, document.typecode, contractorId, rate " +
			"from debtdocument as document left join documentsubtype on documentsubtype.documentsubtypeid = document.documentsubtypeid ";

		private const string RetrieveAllDocumentsQuery = 
			SelectBaseQuery + 
			" where document.typecode = @p0 " +
			"order by docdatetime desc";

		private const string RetrieveDocumentsQuery =
			SelectBaseQuery + 
			" where docdatetime >=@p0 and docdatetime < @p1 " +
			"order by docdatetime";

		public DebtDocuments(FinanceDataBase dataBase) : base(dataBase, "debtdocument")
		{
			IdColumn = "Id";
		}

		public override IDebtDocument GetItem(Guid guid)
		{
			using (var dataReader = DataBase.Sql.ExecuteReader(
				"select debtdocument.*, documentsubtype.name as name from debtdocument inner join " +
				"documentsubtype on documentsubtype.documentsubtypeid = debtdocument.documentsubtypeid " +
				"where debtdocument.id = @p0", guid))
			{
				if (!dataReader.Read())
					throw new Exception("The item not found.");

				return ParseItem(dataReader);
			}
		}

		public override IDebtDocument NewItem()
		{
			return new DebtDocument(this, FinanceDataBase.Contractors);
		}

		public IList<IDebtDocument> RetrieveDocuments(DateTime date)
		{
			var dateStart = new DateTime(date.Year, date.Month, date.Day);
			var dateEnd = dateStart.AddDays(1);

			using (var dataReader = DataBase.Sql.ExecuteReader(RetrieveDocumentsQuery, dateStart, dateEnd))
			{
				var documents = new List<IDebtDocument>();
				while (dataReader.Read())
				{
					var document = ParseItem(dataReader);

					documents.Add(document);
				}
				return documents;
			}
		}

		public IList<IDebtDocument> RetrieveDocuments(DocumentType documentType)
		{
			using (var dataReader = DataBase.Sql.ExecuteReader(RetrieveAllDocumentsQuery, (int)documentType))
			{
				var documents = new List<IDebtDocument>();
				while (dataReader.Read())
				{
					var document = ParseItem(dataReader);

					documents.Add(document);
				}
				return documents;
			}
		}


		protected override IDebtDocument ParseItem(IDataRecord dataReader)
		{
			var document = new DebtDocument(this, FinanceDataBase.Contractors)
			{
				Description = (string)dataReader["Description"],
				Id = (Guid)dataReader["id"],
				DocDate = (DateTime)dataReader["docdatetime"],
				Amount = (decimal)dataReader["amount"],
				SubTypeName = (string)dataReader["name"],
				DocumentType = (DocumentType)(int)dataReader["typecode"],
                Rate = (decimal)dataReader["rate"],
				ContractorId = GetGuid(dataReader, "contractorId")
			};
			return document;
		}

		FinanceDataBase FinanceDataBase
		{
			get { return (FinanceDataBase)DataBase; }
		}

		protected override void Update(IDebtDocument document)
		{
			var subTypeGuid = FinanceDataBase.DocumentSubTypes.CheckSubType(
				document.DocumentType, document.SubTypeName);

			try
			{
				using (var sql = ((FinanceDataBase)DataBase).OpenCommandProcessor())
				{
					sql.TransactionBegin();
					sql.ExecuteNonQuery(
						UpdateDocumentQuery,
						document.Amount,
						subTypeGuid,
						document.Description ?? "",
						document.ContractorId,
						document.DocDate,
						document.Id,
						document.Rate);
					sql.TransactionCommit();
				}
			}
			catch (Exception exception)
			{
				throw new Exception("Ошибка обновления данных документа в базе", exception);
			}
		}

		protected override void Insert(IDebtDocument document)
		{
			var documentSubTypeId = FinanceDataBase.DocumentSubTypes.CheckSubType(
				document.DocumentType, document.SubTypeName);

			try
			{
				using (var sql = ((FinanceDataBase)DataBase).OpenCommandProcessor())
				{
					sql.TransactionBegin();
					document.Id = Guid.NewGuid();
					sql.ExecuteNonQuery(InsertDocumentQuery,
						document.DocDate,
						document.Amount,
						(int)document.DocumentType,
						documentSubTypeId,
						document.Description ?? "",
						document.ContractorId,
						document.Id,
						document.Rate);
					sql.TransactionCommit();
				}
			}
			catch (Exception exception)
			{
				throw new Exception("Ошибка сохранения данных документа в базе", exception);
			}
		}

		public override void Save(object item)
		{
			Save((DebtDocument)item);
		}
	}
}
